package com.yixun.ssm001;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;


public class DBCreatedByExcel {

	//excel中需要过滤掉的sheet
	private static Integer filterSheet[] = {0,1};
	/**
	 * 创建表
	 * @param filePath
	 * @param lastTableName
	 * @throws Exception
	 */
	public static void createSql(String filePath, String lastTableName) throws Exception{
		//过滤Sheet转成set
		Set<Integer> set = new HashSet<Integer>(Arrays.asList(filterSheet));
		StringBuffer sb  = new StringBuffer();
		
		File file = new File(filePath);
		HSSFWorkbook wb = new HSSFWorkbook();
		InputStream is = new FileInputStream(file);
		POIFSFileSystem fs = new POIFSFileSystem(is);
		wb = new HSSFWorkbook(fs);
		//所有标签页
		int sheetNum = wb.getNumberOfSheets();
		HSSFSheet sheet = null;
		String new_tableName_en = "";
		String new_tableName_zh = "";
		String old_tableName_zh = "";
		for(int ii=0; ii<sheetNum; ii++){
			//判断是不是拿需要被过滤的Sheet
			if(set.contains(ii)){
				continue;
			}
			sheet = wb.getSheetAt(ii);
			//标签页下的行数
			int rowNum = sheet.getLastRowNum();
			int rowSize=rowNum+1;
			HSSFRow row = null;
			for(int jj=0; jj<rowSize; jj++){
				row = sheet.getRow(jj);
				//行下面的列
				if(row != null){
					int cellNum = row.getPhysicalNumberOfCells();
					HSSFCell cell = row.getCell(1);
					String one = getCellValue(cell);
					int oneType = cell.getCellType();
					cell = row.getCell(2);
					String two = getCellValue(cell).trim();
					cell = row.getCell(3);
					String three = getCellValue(cell).trim();
					cell = row.getCell(4);
					String four = getCellValue(cell).trim();
					cell = row.getCell(5);
					String five = getCellValue(cell).trim();
					cell = row.getCell(6);
					String six = getCellValue(cell).trim();
					cell = row.getCell(7);
					String seven = getCellValue(cell).trim();
					if(one.indexOf("表名：") >= 0){
						new_tableName_en = one.substring((one.indexOf("（")+1), one.indexOf("）"));
						new_tableName_zh = one.substring((one.indexOf("表名：")+3), one.indexOf("（"));
						if(StringUtils.isEmpty(old_tableName_zh)){
							old_tableName_zh = new_tableName_zh;
						}
						if(StringUtils.isNotEmpty(sb.toString())){
							sb.append(") COMMENT '" + old_tableName_zh + "';");
							sb.append("\n");
						}
						if(!old_tableName_zh.equals(new_tableName_zh)){
							old_tableName_zh = new_tableName_zh;
						}
						sb.append("DROP TABLE IF EXISTS " +new_tableName_en+ ";\n");
						sb.append("CREATE TABLE " +new_tableName_en+ " (\n");
					}
					if(HSSFCell.CELL_TYPE_NUMERIC == oneType){
						if(StringUtils.isEmpty(seven)){
							seven = two;
						}
						String isNull = "null";
						if("否".equals(six)){
							isNull = "not null";
						}
						if("id".equals(three)){
							sb.append("\t" + three + " " + four + "("+five+") " + isNull + " primary key AUTO_INCREMENT COMMENT '"+seven+"',");
						}else{
							if("datetime".equalsIgnoreCase(four)){
								sb.append("\t" + three + " " + four + " " + isNull + " COMMENT '"+seven+"',\n");
							}else if(four.indexOf("decimal") >= 0){
								sb.append("\t" + three + " " + four + isNull + " COMMENT '"+seven+"',\n");
							}else{
								sb.append("\t" + three + " " + four + "("+five+") " + isNull + " COMMENT '"+seven+"',\n");
							}
						}
					}
				}
			}
		}
		sb.append(") COMMENT '"+lastTableName+"';");
		System.out.println(sb.toString().replaceAll(",\n\\)", "\\)"));
	}
	
	/**
	 * 获取单元格数据
	 * @param cell
	 * @return
	 */
	private static String getCellValue(HSSFCell cell) {
		String cellValue = "";
		DecimalFormat df = new DecimalFormat("##.##");
		if(cell == null){
			return cellValue;
		}
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				cellValue = cell.getRichStringCellValue().getString().trim();
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
	                SimpleDateFormat sdf = null;  
	                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
	                    sdf = new SimpleDateFormat("HH:mm");  
	                } else {// 日期  
	                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
	                }  
	                Date date = cell.getDateCellValue();  
	                cellValue = sdf.format(date);  
	            } else if (cell.getCellStyle().getDataFormat() == 58) {  
	                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
	                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
	                double value = cell.getNumericCellValue();  
	                Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);  
	                cellValue = sdf.format(date);  
	            } else {  
	                double value = cell.getNumericCellValue();  
	                CellStyle style = cell.getCellStyle();  
	                DecimalFormat format = new DecimalFormat();  
	                String temp = style.getDataFormatString();  
	                // 单元格设置成常规  
	                if (temp.equals("General")) {  
	                    format.applyPattern("#.##");  
	                }  
	                cellValue = format.format(value);  
	            }  
				//cellValue = df.format(cell.getNumericCellValue()).toString();
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				cellValue = cell.getCellFormula();
				break;
			default:
				cellValue = "";
		}
		return cellValue;
	}
	
	public static void main(String[] args) {
		try {
			createSql("D:\\workplace\\house_marketing_ERP\\document\\数据库详细设计V1.1.xls","已收/退款表");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
